using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;

namespace MusicEvolution.Database
{
	internal class DatabaseBackend
	{
        private SQLiteConnection _connection;
        static DatabaseBackend _instance;


        private bool Connect(string ConnectionString)
        {
            try
            {
                _connection = new SQLiteConnection(ConnectionString);
                _connection.Open();
                
            }
            catch (System.Exception ex)
            {
                Logging.Logger.LogMessage(Logging.MessageType.Error, "DataBaseBackend.Connect", ex.Message);
                return false;
            }
            return true;
        }

        public static DatabaseBackend Backend
        {
            get { return _instance; }
        }

		public List<DataFieldList> ExecuteQuery(string sqlCommand)
		{
            if (_connection.State != ConnectionState.Open)
            {
                Logging.Logger.LogMessage(Logging.MessageType.Warning, "DataBaseBackend.ExecuteQuery", "Trying to access database with closed connection. Querystring: "+sqlCommand);
                return null;
            }
            List<DataFieldList> retVal =new List<DataFieldList>();
            SQLiteCommand command = new SQLiteCommand(sqlCommand, _connection);
            SQLiteDataReader reader = command.ExecuteReader();
            
            while(reader.Read())
            {
                
                DataFieldList list = new DataFieldList();
                for(int i=0;i<reader.FieldCount;i++)
                {
                    DataField field = new DataField(reader.GetName(i), reader[i].ToString());
                    list.Add(field);
                }
                retVal.Add(list);
            }
            return retVal;
		}

        public int ExecuteNonQuery(string sqlcommand)
        {
            if (_connection.State != ConnectionState.Open)
            {
                Logging.Logger.LogMessage(Logging.MessageType.Warning, "DataBaseBackend.ExecuteNonQuery", "Trying to access database with closed connection. Querystring: " + sqlcommand);
                return -1;
            }
            SQLiteCommand cmd = new SQLiteCommand(sqlcommand, _connection);
            cmd.CommandText = sqlcommand;
            return cmd.ExecuteNonQuery();            
        }

		public DataField ExecuteScalar(string SqlCommand)
		{
            if (_connection.State != ConnectionState.Open)
            {
                Logging.Logger.LogMessage(Logging.MessageType.Warning, "DataBaseBackend.ExecuteQuery", "Trying to access database with closed connection. Querystring: " + SqlCommand);
                return null;
            }
            List<DataBaseRow> retVal = new List<DataBaseRow>();
            SQLiteCommand command = new SQLiteCommand(SqlCommand, _connection);
            SQLiteDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                DataField field = new DataField(reader.GetName(0), reader[0].ToString());
                return field;                
            }
            return null;
		}

        public void AddRow(DataBaseRow dr, string TableName)
        {
//            INSERT INTO Store_Information (store_name, Sales, Date)
//              VALUES ('Los Angeles', 900, '10.Jan.1999')
            string insert = "insert into " + TableName;
            string names = "(";
            string values = "(";
            foreach (DataField field in dr.Fields)
            {
                names += field.Name + ",";
                values += "\"" + field.Value + "\"";
            }
            names.Remove(names.Length - 1);
            values.Remove(values.Length - 1);
            names += ")";
            values += ")";
            insert += " " + values + " Values " + values;
            if (_connection.State != ConnectionState.Open)
            {
                Logging.Logger.LogMessage(Logging.MessageType.Warning, "DataBaseBackend.AddRow", "Trying to access database with closed connection. Querystring: " + insert);
                return ;
            }
            SQLiteCommand cmd = new SQLiteCommand(insert, _connection);
            cmd.ExecuteNonQuery();
        }

		public void UpdateRow(List<DataField> fields, long PK,string TableName)
        {
			string update="UPDATE "+TableName+" SET ";
            foreach (DataField field in fields)
            {
                update += field.Name + "=\"" + field.Value + "\"";
                update += ",";
            }
            update = update.Remove(update.Length - 1);
            update += " WHERE ID=" + PK.ToString();

            if (_connection.State != ConnectionState.Open)
            {
                Logging.Logger.LogMessage(Logging.MessageType.Warning, "DataBaseBackend.UpdateRow", "Trying to access database with closed connection. Updating: "+TableName);
                return;
            }
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(update, _connection);
                int res = cmd.ExecuteNonQuery();
                if (res == 0)
                {
                    Logging.Logger.LogMessage(Logging.MessageType.Warning, "DataBaseBackend.UpdateRow", "Update resulted in no changed rows: "+update);
                }
            }
            catch (Exception ex)
            {
                Logging.Logger.LogMessage(Logging.MessageType.Error, "DataBaseBackend.UpdateRow", "Error executing table update on table " + TableName+". Errormessage: "+ex.ToString());
            }
            
		}
		
		public bool Initialize(string ConnectionString)
		{
            _instance = new DatabaseBackend();
            Connect(ConnectionString);
            return true;
		}

		public bool AddRow(List<DataField> fields, string Table)
		{
			throw new NotImplementedException();
		}
	}
}
